package com.allen.service;

import com.allen.Utils.JdbcUtils;
import com.allen.bean.Stock;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StockService {
    private Connection connection;
    private Statement statement;
    private PreparedStatement preparedStatement;
    private ResultSet resultSet;


    public List<Stock> getAll() {
        List<Stock> stocks = new ArrayList<>();
        String sql = "SELECT s.gid,g.gname,g.merc,g.place,s.number FROM goods g JOIN stock s ON g.gid = s.gid;";
        connection = JdbcUtils.getConnect();
        try {
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Stock stock = new Stock();
                stock.setGid(resultSet.getInt("gid"));
                stock.setGname(resultSet.getString("gname"));
                stock.setMerc(resultSet.getString("merc"));
                stock.setPlace(resultSet.getString("place"));
                stock.setNumber(resultSet.getInt("number"));
                stocks.add(stock);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return stocks;
    }

    public void updateNum(HttpServletRequest req, HttpServletResponse resp) {
        Integer num = Integer.parseInt(req.getParameter("num"));
        Integer gid = Integer.parseInt(req.getParameter("gid"));
        String sql = "UPDATE stock SET number=? WHERE gid=?";

        connection = JdbcUtils.getConnect();
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, num);
            preparedStatement.setInt(2, gid);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    public void add(Integer id) {
        String sql = "insert into stock (number,gid) values(?,?)";
        connection = JdbcUtils.getConnect();
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, 0);
            preparedStatement.setInt(2, id);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
